package cn.group_06.bookmanager.dao;

import cn.group_06.bookmanager.model.sp;




import cn.group_06.bookmanager.utils.toolUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.sql.SQLException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

public class spDao {
	

	
	public int addUser(Connection con, sp user) throws Exception{
		//鏌ヨ娉ㄥ唽鐢ㄦ埛鍚嶆槸鍚﹀瓨鍦�
		String sql = "select * from sp where xsname=? ";
	
	    sql="insert into sp (id,name,dj,gysbh,jj,bz) values (?,?,?,?,?,?)";
	    PreparedStatement pstmt2=(PreparedStatement) con.prepareStatement(sql);
		pstmt2.setInt(1, user.getId());
		pstmt2.setString(2, user.getName());
		
		pstmt2.setString(3, String.valueOf(user.getDj()));
		pstmt2.setString(4,  String.valueOf(user.getGysbh()) );
		pstmt2.setString(5,  String.valueOf(user.getJj()) );
		pstmt2.setString(6,  String.valueOf(user.getBz()) );
		
		//默认要改
		/*
		 sql="insert into sp (id,name,gysbh,jj,bz) values (?,?,?,?,?)";
		    PreparedStatement pstmt2=(PreparedStatement) con.prepareStatement(sql);
			pstmt2.setInt(1, user.getId());
			pstmt2.setString(2, user.getName());
			//pstmt2.setString(3, String.valueOf(user.getDj()));
			pstmt2.setString(3,  String.valueOf(user.getGysbh()) );
			pstmt2.setString(4,  String.valueOf(user.getJj()) );
			pstmt2.setString(5,  String.valueOf(user.getBz()) );
			*/
		//

		return pstmt2.executeUpdate();
	}
	
	
	public ResultSet list(Connection con,sp user)throws Exception{
		StringBuffer sb=new StringBuffer("select * from sp ");

		if(!toolUtil.isEmpty(user.getName()) ){
			sb.append(" where name like '%"+user.getName()+"%'");
		}
		PreparedStatement pstmt=(PreparedStatement) con.prepareStatement(sb.toString());
		return pstmt.executeQuery();
	}

	public int update(Connection con, sp user) throws Exception {
	    String sql = "UPDATE sp SET name=?, dj=?, gysbh=?, jj=?, bz=? WHERE id=?";
	    try (PreparedStatement pstmt = con.prepareStatement(sql)) {
	    
	    
	      
	        pstmt.setString(1, user.getName());
	        pstmt.setString(2, String.valueOf(user.getDj()));
	        pstmt.setString(3, String.valueOf(user.getGysbh()));
	        pstmt.setString(4, String.valueOf(user.getJj()));
	        pstmt.setString(5, String.valueOf(user.getBz()));
	    
	    	pstmt.setInt(6, user.getId());
	        
	        // 执行更新操作并返回受影响的行数
	        return pstmt.executeUpdate();
	    }
	}

	public int del(Connection con,sp user)throws Exception{
		String sql="delete from sp  where id=?";
		PreparedStatement pstmt=(PreparedStatement) con.prepareStatement(sql);
		pstmt.setInt(1, user.getId());
		return pstmt.executeUpdate();
	}

//主码重复
	public boolean existsId(Connection con, int id) throws SQLException {
	    String sql = "SELECT COUNT(*) FROM sp WHERE id = ?";
	    try (PreparedStatement pstmt = con.prepareStatement(sql)) {
	        pstmt.setInt(1, id);
	        ResultSet rs = pstmt.executeQuery();
	        if (rs.next()) {
	            return rs.getInt(1) > 0;
	        }
	    }
	    return false;
	}
	//

	public void WriteExcel(List<sp> list, String filepath) throws IOException {

		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("鍟嗗搧淇℃伅");
		HSSFRow row = sheet.createRow(0);
		String[] s = { "num", "name", "dj", "gysbh", "jj","bz" };
		for (int i = 0; i < 6; i++) {
			row.createCell(i).setCellValue(s[i]);

		}

		for (int j = 0; j < list.size(); j++) {
			HSSFRow r = sheet.createRow(j + 1);
			r.createCell(0).setCellValue(list.get(j).getId());
			r.createCell(1).setCellValue(list.get(j).getName());
			r.createCell(2).setCellValue(list.get(j).getDj());
			r.createCell(3).setCellValue(list.get(j).getGysbh());
			r.createCell(4).setCellValue(list.get(j).getJj());
			r.createCell(5).setCellValue(list.get(j).getBz());

		}
		OutputStream out = new FileOutputStream(filepath);
		workbook.write(out);
		System.out.println("瀵煎嚭鎴愬姛锛�");
		out.close();
	}
}
